-- =============================================
-- This stored procedure takes two ContentIDs from same Nav_Menu
-- and move the first one below the second one.
--
-- Modifications
-- 08/20/2003  E.Tatsui
-- =============================================

CREATE              PROCEDURE amsp_CMMoveContent
  @InMoveContentID numeric,
  @InTargetContentID numeric    
AS
BEGIN
  DECLARE
    @NavMenuID numeric,
    @TargetSortOrder numeric,
    @MoveSortOrder numeric

  -- If TargetSortOrder is 0, it means it wants to go to the top of the list.
  IF @InTargetContentID = 0 BEGIN
    SET @TargetSortOrder = 0
    SELECT @NavMenuID = NavMenuID
      FROM Content WITH (NOLOCK)
     WHERE ContentID = @InMoveContentID
  END
  ELSE BEGIN
    SELECT @NavMenuID = a.NavMenuID,
           @TargetSortOrder = IsNull(b.SortOrder,0),
           @MoveSortOrder = IsNull(a.SortOrder, 0)
      FROM Content a WITH (NOLOCK), Content b WITH (NOLOCK), Nav_Menu c WITH (NOLOCK)
     WHERE a.NavMenuID = b.NavMenuID
       AND a.ContentID = @InMoveContentID
       AND b.ContentID = @InTargetContentID
       AND a.NavMenuID = c.NavMenuID


    IF @@RowCount = 0 BEGIN
      RAISERROR ('The record to move and target record must have same NavMenuID.', 16, 1)
      RETURN
    END
  END

  -- Move everything below target by one.
  UPDATE Content
     SET SortOrder = IsNull(SortOrder,1) + 1
   WHERE NavMenuID = @NavMenuID
     AND SortOrder > @TargetSortOrder
     AND ContentID <> @InMoveContentID

  -- Move the request item right below the target.
  UPDATE Content
     SET SortOrder = @TargetSortOrder + 1
   WHERE ContentID = @InMoveContentID

  -- If SortOrder was changed to 1, make it the default content.
  IF @TargetSortOrder = 0
    UPDATE Nav_Menu
       SET ContentID = @InMoveContentID,
           WorkflowStatusCode = 'W',
           PublishedDateTime = NULL,
           ComponentCode = 'CM',
           ComponentScriptCode = 'SC'
     WHERE NavMenuID = @NavMenuID
       AND ContentID <> @InMoveContentID

  -- Now, re-sort everything.
  EXEC amsp_CMRenumCurrentContent @NavMenuID

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMMoveContent] TO [IMIS]
GO